Table of Contents

• 1  Research purpose
• 2  Research description
• 3  Data description
• 4  Data preprocessing
  • 4.1  Creating a "street" column with street names
  • 4.2  Creating a column "is_24/7" with for round-the-clock and daily establishments
  • 4.3  Adjusting the name column
  • 4.4  Adjusting the category column
  • 4.5  Interim summary
• 5  Data analysis
  • 5.1  Establishments category distribution
  • 5.2  Number of seats in establishments by category visualization
  • 5.3  Network and non-network establishments ratio
  • 5.4  Categories of chain establishments research
  • 5.5  Top-15 popular chain in Moscow
  • 5.6  Distribution of establishments of each category by administrative districts of Moscow
  • 5.7  Distribution of average ratings by categories of establishments
  • 5.8  Cartogram of Moscow districts with an average rating of establishments
  • 5.9  Establishments on the map of Moscow
  • 5.10  Top 15 streets by number of establishments
  • 5.11  Distribution chart of number of establishments and their categories by Top-15 streets
  • 5.12  Streets with a single catering facility
  • 5.13  The effect of distance from the center on prices in establishments
  • 5.14  Dependence of the opening hours of establishments on the location and category of the institution
  • 5.15  Distribution of establishments with poor ratings by districts and categories
  • 5.16  Average check of establishments with poor ratings by category
  • 5.17  Interim summary
• 6  Details of the study "opening a coffee shop"
  • 6.1  Distribution of coffee shops by districts and features of their location
  • 6.2  24-hour coffee shops
  • 6.3  Distribution of coffee shop ratings by district
  • 6.4  Average cost of a cup of cappuccino by district
  • 6.5  Interim summary
• 7  General recommendation for opening a new coffee shop
  • 7.1  Recommendation for opening a new coffee shop. Option 1
  • 7.2  Recommendation for opening a new coffee shop. Option 2

Moscow public catering establishments market research

Research purpose

Explore the market of public catering establishments in Moscow and find interesting features that will help in choosing a suitable place to open a new establishment.

Research description

Investors of the Shut Up and Take My Money fund decided to open a catering establishment in Moscow. The location, menu, prices and type of establishment have not yet been determined.

The task is to prepare a study of the Moscow market, find interesting features and present the results obtained, which willl help investors to choosie a suitable place. We have a dataset with public catering establishments in Moscow, compiled on the basis of data from Yandex Maps and Yandex Business services for the summer of 2022.

Data description

File moscow_places.csv:

• name — the name of the institution;
• address — the address of the institution;
• category — the category of the institution, for example, "cafe", "pizzeria" or "coffee shop";
• hours — information about working days and hours;
• lat — latitude of the geographical point where the institution is located;
• lng — longitude of the geographical point where the institution is located;
• rating — rating of the institution according to user ratings in Yandex Maps (the highest rating is 5.0);
• price — the category of prices in the institution, for example, "average", "below average", "above average" and so on;
• avg_bill — a string that stores the average cost of an order as a range, for example: "Average bill: 1000-1500 ₽"; "Price of a cup of cappuccino: 130-220 ₽"; "Price of a glass of beer: 400-600 ₽" and so on;
• middle_avg_bill — a number with an estimate of the average check, which is specified only for values from the avg_bill column starting with the substring "Average bill": If a price range of two values is specified in the row, the median of these two values will be included in the column. If a single number is specified in the row — the price without a range, then this number will be included in the column. If there is no value or it does not start with the substring "Average score", then nothing will be included in the column.
• middle_coffee_cup — a number with an estimate of one cup of cappuccino, which is specified only for values from the avg_bill column starting with the substring "Price of one cup of cappuccino": If a price range of two values is specified in the row, the median of these two values will be included in the column. If a single number is specified in the row — the price without a range, then this number will be included in the column. If there is no value or it does not start with the substring "Price of one cup of cappuccino", then nothing will be included in the column.
• chain — a number expressed as 0 or 1, which indicates whether the institution is a network (errors may occur for small networks);
• district — the administrative district in which the institution is located, for example, the Central Administrative District;
• seats — the number of seats.

import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
import plotly.express as px
from plotly import graph_objects as go
import folium
from folium import Map, Choropleth, Marker
from folium.plugins import MarkerCluster
from folium.features import CustomIcon
from numpy import median
import os
import json

pth1 = 'moscow_places.csv'
pth2 = 'datasets/moscow_places.csv'

if os.path.exists(pth1):
    data = pd.read_csv(pth1, sep=',')
elif os.path.exists(pth2):
    data = pd.read_csv(pth2, sep=',')
else:
    print('Path not found')

Data preprocessing

#
print('------------- First 5 lines ------------')
display(data.sample(5))
print('------------- Data types ------------')
display(data.info())
print('------------- Gaps ------------')
for element in data.columns:
    if data[element].isna().any().mean() > 0:
        print(element, '-', data[element].isna().sum())
    else:
        print(element, '- None')
print('------------- Duplicates ------------')
if data.duplicated().sum() > 0:
    print(data.duplicated().sum())
else:
    print('No Duplicates');

------------- First 5 lines ------------

	name	category	address	district	hours	lat	lng	rating	price	avg_bill	middle_avg_bill	middle_coffee_cup	chain	seats
3326	Pizzeria 360°	пиццерия	Москва, Кутузовский проспект, 57	Западный административный округ	пн-чт 10:00–22:00; пт,сб 10:00–23:00; вс 10:00...	55.728254	37.477693	4.0	средние	Средний счёт:200–300 ₽	250.0	NaN	0	96.0
690	Семейный ресторан Boobo	бар,паб	Москва, Дмитровское шоссе, 73, стр. 1	Северный административный округ	пн-чт 11:00–23:00; пт,сб 11:00–05:00; вс 11:00...	55.857159	37.558842	4.4	высокие	Средний счёт:1500–2000 ₽	1750.0	NaN	0	350.0
2136	Прогресс	бар,паб	Москва, улица Гиляровского, 68с1	Центральный административный округ	ежедневно, 11:00–00:00	55.788796	37.633259	4.9	NaN	NaN	NaN	NaN	1	NaN
475	Korean Chick	ресторан	Москва, Кронштадтский бульвар, 3, стр. 1	Северный административный округ	ежедневно, 10:00–22:00	55.839453	37.486452	4.2	NaN	NaN	NaN	NaN	1	226.0
6302	Coffee Place	кафе	Москва, улица Шаболовка, 10	Центральный административный округ	пн-пт 09:00–20:00	55.723694	37.610286	4.4	средние	Средний счёт:800–1000 ₽	900.0	NaN	0	NaN

------------- Data types ------------
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 8406 entries, 0 to 8405
Data columns (total 14 columns):
 #   Column             Non-Null Count  Dtype  
---  ------             --------------  -----  
 0   name               8406 non-null   object 
 1   category           8406 non-null   object 
 2   address            8406 non-null   object 
 3   district           8406 non-null   object 
 4   hours              7870 non-null   object 
 5   lat                8406 non-null   float64
 6   lng                8406 non-null   float64
 7   rating             8406 non-null   float64
 8   price              3315 non-null   object 
 9   avg_bill           3816 non-null   object 
 10  middle_avg_bill    3149 non-null   float64
 11  middle_coffee_cup  535 non-null    float64
 12  chain              8406 non-null   int64  
 13  seats              4795 non-null   float64
dtypes: float64(6), int64(1), object(7)
memory usage: 919.5+ KB

None

------------- Gaps ------------
name - None
category - None
address - None
district - None
hours - 536
lat - None
lng - None
rating - None
price - 5091
avg_bill - 4590
middle_avg_bill - 5257
middle_coffee_cup - 7871
chain - None
seats - 3611
------------- Duplicates ------------
No Duplicates

---

There are 8406 establishments in operation. No duplicates were found. There are gaps in secondary values - at this stage of the work we do not fill them in. It may not be necessary in the future.

Creating a "street" column with street names

data['street'] = data['address'].str.split(', ').apply(lambda x: x[1])

Creating a column "is_24/7" with for round-the-clock and daily establishments

def check_247(value):
    try:
        if ('круглосуточно' in value) and ('ежедневно' in value):
            return True
        return False
    except: return False
        
data['is_24/7'] = data['hours'].apply(check_247)

# Let's check the correctness of the new columns
data[['street','is_24/7']].describe()

	street	is_24/7
count	8406	8406
unique	1448	2
top	проспект Мира	False
freq	184	7676

Adjusting the name column

# Replace the string values of the name column with lowercase
data['name'] = data['name'].str.lower()
data['name'] = data['name'].replace('\.',' ', regex=True)
data = data.rename(columns={'event.time':'event_time', 'event.name':'event_name', 'user.id':'user_id'})
data = data.replace({
    'category': {
        'бар,паб': 'bar,pub',
        'булочная':'bakery', 
        'быстрое питание':'fast food', 
        'кафе':'cafe', 
        'кофейня':'coffee_shop', 
        'пиццерия':'pizzeria', 
        'ресторан':'restaurant', 
        'столовая':'canteen',         
    }
})

Adjusting the category column

b = data.pivot_table(index='name', columns = 'category', values = 'lat', aggfunc = 'count')
b = b.fillna(0.0)
b['category_major'] = b.apply(lambda row: row.idxmax(), axis=1)
b = b.reset_index()
display(b.sample(5))

category	name	bakery	bar,pub	cafe	canteen	coffee_shop	fast food	pizzeria	restaurant	category_major
4549	стейк&паб	0.0	1.0	0.0	0.0	0.0	0.0	0.0	0.0	bar,pub
1896	бакинский дворик	0.0	0.0	0.0	0.0	0.0	0.0	0.0	1.0	restaurant
3307	лаки сувлаки	0.0	0.0	0.0	0.0	0.0	4.0	0.0	0.0	fast food
629	free & со	0.0	0.0	0.0	0.0	0.0	0.0	1.0	0.0	pizzeria
913	le круассан	0.0	0.0	0.0	2.0	0.0	0.0	0.0	0.0	canteen

display(b[b['name'] =='кафе'])

category	name	bakery	bar,pub	cafe	canteen	coffee_shop	fast food	pizzeria	restaurant	category_major
2825	кафе	0.0	2.0	159.0	6.0	6.0	7.0	1.0	8.0	cafe

# set the same categories for each chain of establishments 
b = b[['name','category_major']]
data = data.merge(b, on ='name', how='left')
# Заменим названия районов на аббревиатуры
distr_dict = {'Северный административный округ':'САО',
       'Северо-Восточный административный округ':'СВАО',
       'Северо-Западный административный округ':'СЗАО',
       'Западный административный округ':'ЗАО',
       'Центральный административный округ':'ЦАО',
       'Восточный административный округ':'ВАО',
       'Юго-Восточный административный округ':'ЮВАО',
       'Южный административный округ':'ЮАО',
       'Юго-Западный административный округ':'ЮЗАО'}

data['district_abb'] = data['district'].apply(lambda x: distr_dict[x] if x in distr_dict.keys() else "ERROR")
display(data.sample(5))

	name	category	address	district	hours	lat	lng	rating	price	avg_bill	middle_avg_bill	middle_coffee_cup	chain	seats	street	is_24/7	category_major	district_abb
3809	maya	restaurant	Москва, Большая Никитская улица, 24/1с6	Центральный административный округ	пн-чт 09:00–00:00; пт,сб 09:00–02:00; вс 09:00...	55.757144	37.601956	4.4	NaN	NaN	NaN	NaN	0	NaN	Большая Никитская улица	False	restaurant	ЦАО
2714	гурман	restaurant	Москва, Щербаковская улица, 53к15	Восточный административный округ	ежедневно, круглосуточно	55.783006	37.734446	4.3	выше среднего	Средний счёт:1000–1500 ₽	1250.0	NaN	1	NaN	Щербаковская улица	True	cafe	ВАО
7614	чайная	cafe	Москва, Новоясеневский проспект, 42	Юго-Западный административный округ	NaN	55.600413	37.549440	5.0	NaN	NaN	NaN	NaN	1	NaN	Новоясеневский проспект	False	cafe	ЮЗАО
3704	винил и вино	bar,pub	Москва, улица Каретный Ряд, 8	Центральный административный округ	пн-чт 12:00–00:00; пт,сб 12:00–05:00; вс 12:00...	55.772747	37.609668	4.7	высокие	NaN	NaN	NaN	0	NaN	улица Каретный Ряд	False	bar,pub	ЦАО
5814	coffee and the city	coffee_shop	Москва, Сколковское шоссе, 31, стр. 1	Западный административный округ	ежедневно, 10:00–21:00	55.705506	37.403942	4.0	низкие	Цена чашки капучино:184–194 ₽	NaN	189.0	1	50.0	Сколковское шоссе	False	coffee_shop	ЗАО

Interim summary

There are 8406 establishments in operation. No duplicates were found. There are gaps in secondary values - at this stage of the work we do not fill them in. It may not be necessary in the future.

The gaps in the hours - 536 values make 6%. In the conditions of this experiment, this amount can be ignored. Working hours are not a critical characteristic in the study. The gaps in each of the price, avg_bill, middle_avg_bill, middle_coffee_cup columns are more than 60%, such losses are irreplaceable, and also these data are not critical in the study, since we were not tasked with determining the exact cost of a cup of coffee. And to define the general concept of the price category, this data is enough. The gaps in the seats column account for more than a third - these data are also not the subject of detailed study in this study.

The names are reduced to lowercase to reduce non-obvious duplicates.

The categories of chain establishments are reduced to one value.

Data analysis

Establishments category distribution

px.defaults.template = "simple_white"
cat = data['category'].value_counts().reset_index()
fig = px.pie(cat, values = cat['category'], names=cat['index'], 
             title='Establishments category distribution', color_discrete_sequence= px.colors.qualitative.Vivid)
fig

---

• Cafes predominate in the categories -28%, restaurants -24% are slightly behind, followed by coffee shops - 16%
• Bakeries are the most unpopular

Number of seats in establishments by category visualization

order=data.groupby('category').mean().sort_values('seats', ascending=False).index
order

Index(['bar,pub', 'restaurant', 'coffee_shop', 'canteen', 'fast food', 'cafe',
       'pizzeria', 'bakery'],
      dtype='object', name='category')

sns.set_style("ticks")
sns.set_palette('Paired')
plt.figure(figsize=(16,6))
order=data.groupby('category').mean().sort_values('seats', ascending=False).index
fig = sns.barplot(x='category', y='seats', data=data, order=order, ci=None)
fig.set_title('Number of seats in establishments by category')
fig.set_xlabel('Category') 
fig.set_ylabel('Number of seats');

---

• The median number of seats for a coffee shop is 70-80.
• The most seats in restaurants and bars, pubs - 80-90 seats.

Network and non-network establishments ratio

data = data.replace({'chain':{0:'non-chain',1:'chain'}}, regex=True)

ch = data['chain'].value_counts().reset_index()
fig = px.pie(ch, values = ch['chain'], names = ch['index'], 
             title='Chain and non-chain establishments ratio', color_discrete_sequence= px.colors.qualitative.Vivid)
fig

---

• The majority of non-chain establishments - 62%, chain - 38%

Categories of chain establishments research

d = data.pivot_table(index='category',columns='chain', values='lat', aggfunc='count').reset_index()
d['sum'] = d['chain']+d['non-chain']
d['chain_ratio'] = d['chain']/d['sum']
d = d.sort_values('chain_ratio', ascending=False)

d['chain_ratio'] = d['chain_ratio'].astype(str)
fig = px.histogram(d, x='category', y='chain_ratio', 
                   color_discrete_sequence= px.colors.qualitative.Vivid)
fig.update_layout(title='Categories of chain establishments', 
                  xaxis_title='Categories',
                  yaxis_title='Ratio of chain establishments')

fig.show()

---

• Most often seeded establishments are bakeries, pizzerias, coffee shops
• The most unpopular category of chain establishments - bar, pub

Top-15 popular chain in Moscow

Making a list of the top 15 establishments

d1 = data[data['chain']=='chain']['name'].value_counts().reset_index()
chain15 = d1.loc[:14,'index']
display(d1.head(15))

	index	name
0	шоколадница	120
1	домино'с пицца	76
2	додо пицца	74
3	яндекс лавка	72
4	one price coffee	71
5	cofix	65
6	prime	50
7	хинкальная	44
8	кофепорт	42
9	кулинарная лавка братьев караваевых	39
10	теремок	38
11	чайхана	37
12	cofefest	32
13	буханка	32
14	му-му	27

We build the corresponding histogram of the most popular establishments

d2 = data.query('chain =="chain" & name in @chain15')
fig = px.histogram(d2, x='name', color='category',  
                   color_discrete_sequence= px.colors.qualitative.Vivid)
fig.update_layout(xaxis={'categoryorder':'total descending'})
fig.update_layout(title='Top-15 popular chain in Moscow',
                  yaxis_title='Number of establishments', 
                  xaxis_title='Name of establishments')
fig.show()

d5 = d2.groupby(['district_abb','category'])['lat'].count().reset_index()
fig = px.bar(d5, x='district_abb', y= 'lat', color='category', 
                   color_discrete_sequence= px.colors.qualitative.Vivid)
fig.update_layout(xaxis={'categoryorder':'total descending'})
fig.update_layout(title='Distribution of the top 15 chain establishments by districts of Moscow', 
                  yaxis_title='Number of establishments', 
                  xaxis_title='Administrative district of Moscow')
fig.show()

display(d2.groupby('name').agg({'category':'first', 
                                'rating':'median', 
                                'price':'first',
                                'middle_avg_bill':'median', 
                                'middle_coffee_cup':'median',
                                'lat':'count'}).reset_index().sort_values('lat', ascending=False))

	name	category	rating	price	middle_avg_bill	middle_coffee_cup	lat
13	шоколадница	coffee_shop	4.20	средние	650.0	256.0	120
6	домино'с пицца	pizzeria	4.20	средние	500.0	NaN	76
5	додо пицца	pizzeria	4.30	средние	391.5	NaN	74
14	яндекс лавка	restaurant	4.00	None	NaN	NaN	72
2	one price coffee	coffee_shop	4.20	средние	NaN	80.0	71
1	cofix	coffee_shop	4.10	средние	NaN	60.0	65
3	prime	restaurant	4.20	низкие	300.0	NaN	50
11	хинкальная	fast food	4.40	средние	1000.0	NaN	44
7	кофепорт	coffee_shop	4.20	низкие	NaN	95.0	42
8	кулинарная лавка братьев караваевых	cafe	4.40	средние	450.0	NaN	39
10	теремок	restaurant	4.10	средние	325.0	NaN	38
12	чайхана	cafe	4.10	средние	400.0	NaN	37
0	cofefest	coffee_shop	4.05	средние	512.5	95.0	32
4	буханка	bakery	4.40	средние	237.5	NaN	32
9	му-му	cafe	4.30	средние	450.0	NaN	27

display('Median rating of F establishments' ,d2['rating'].median())
display('Median average check of chain establishments' ,d2['middle_avg_bill'].median())

'Median rating of F establishments'

4.2

'Median average check of chain establishments'

415.0

---

Establishments, in addition to their popularity, are united by the following factors:

• price category - average
• median rating above 4

Among the Top 15 chain establishments in Moscow, the first place is the coffee shop Chocolate, pizzeria, Yandex-Shop restaurant, and budget coffee shops

Distribution of establishments of each category by administrative districts of Moscow

display(f'Total number of establishments - {len(data)}')

'Total number of establishments - 8406'

distr = data.pivot_table(index='category', columns='district_abb', values='name', aggfunc='count')

plt.figure(figsize=(15,15))
sns.set(font_scale=2)
fig = sns.heatmap(distr, annot=True, fmt='g', linewidth=.5, annot_kws={"fontsize":20})
sns.set(font_scale=1)
plt.xticks(rotation=70)
fig.set(xlabel=None, ylabel=None)
fig.set_title('Heat map of categories by districts of Moscow');

distr2 = data.groupby(['district_abb','category'])['lat'].count().reset_index()

fig = px.bar(distr2, x='district_abb', y='lat', color='category',
                   color_discrete_sequence= px.colors.qualitative.Vivid)
fig.update_layout(xaxis={'categoryorder':'total descending'})
fig.update_layout(title='Distribution of the top 15 chain establishments by districts of Moscow', 
                  yaxis_title='Number of establishments', 
                  xaxis_title='Administrative district of Moscow')
fig.show()

---

• Most of the establishments are in the Central JSC of Moscow. 658 Restaurants, 461 cafes, 425 coffee shops, 360 bars
• Cafes - about 260 establishments and restaurants - about 170 establishments in the district will prevail in all other districts
• The number of bars in the Centeal AD is 5 times higher than the average of other districts
• Bakeries and canteens are the most unpopular categories in all districts

Distribution of average ratings by categories of establishments

sns.set_style("ticks")
sns.set_palette('Paired')
plt.figure(figsize=(16,6))
order=data.groupby('category').mean().sort_values('rating', ascending=False).index
fig = sns.barplot(x='category', y='rating', data=data, order=order, ci=None)
fig.set_ylim(4,4.5)
fig.set_title('Average ratings by category of establishments')
fig.set_xlabel('Category') 
fig.set_ylabel('Rating');

---

• The highest rating by category at bars
• The lowest at fast food

Cartogram of Moscow districts with an average rating of establishments

#
moscow_lat, moscow_lng = 55.751244, 37.618423


pth3 = 'admin_level_geomap.geojson'
pth4 = 'datasets/admin_level_geomap.geojson'

if os.path.exists(pth3):
    with open(pth3, 'r',  encoding='utf-8') as f:
        state_geo = json.load(f)
elif os.path.exists(pth4):
    with open(pth4, 'r',  encoding='utf-8') as f:
        state_geo = json.load(f)
else:
    state_geo = 'admin_level_geomap.geojson'
    
rating = data.groupby('district', as_index=False)['rating'].agg('mean')

m = Map(location=[moscow_lat, moscow_lng], zoom_start=10)

Choropleth(
    geo_data=state_geo,
    data=rating,
    columns=['district', 'rating'],
    key_on='feature.name',
    fill_color='YlOrRd',
    fill_opacity=0.5,
    legend_name='Average rating of establishments by district').add_to(m)
m

Make this Notebook Trusted to load map: File -> Trust Notebook

---

• The highest average rating of establishments in the Central AD is 4.38, followed by NAD and NWAD
• The lowest average rating of the Southern Administrative District

Establishments on the map of Moscow

#
m = Map(location=[moscow_lat, moscow_lng], zoom_start=10)
marker_cluster = MarkerCluster().add_to(m)

def create_clusters(row):
    icon_url = 'https://img.icons8.com/parakeet/344/restaurant-building.png'
    icon = CustomIcon(icon_url, icon_size=(30,30))

    Marker(
        [row['lat'], row['lng']],
        popup=f"{row['name']} {row['rating']}",
        icon=icon
    ).add_to(marker_cluster)

data.apply(create_clusters, axis=1)

m

Make this Notebook Trusted to load map: File -> Trust Notebook

Top 15 streets by number of establishments

street_top = data['street'].value_counts().reset_index()
street_top = street_top.loc[:14,'index']
street_top

0              проспект Мира
1          Профсоюзная улица
2       проспект Вернадского
3         Ленинский проспект
4     Ленинградский проспект
5          Дмитровское шоссе
6            Каширское шоссе
7           Варшавское шоссе
8        Ленинградское шоссе
9                       МКАД
10          Люблинская улица
11            улица Вавилова
12      Кутузовский проспект
13      улица Миклухо-Маклая
14           Пятницкая улица
Name: index, dtype: object

Distribution chart of number of establishments and their categories by Top-15 streets

d3 = data.query('street in @street_top')
fig = px.histogram(d3, x='street', color='category', 
                   color_discrete_sequence= px.colors.qualitative.Vivid)
fig.update_layout(xaxis={'categoryorder':'total descending'})
fig.update_layout(title='Top 15 streets of Moscow', 
                  xaxis_title='Street names',
                  yaxis_title='Number of establishments')
fig.show()

---

• The most popular street is Mira Avenue
• on the 15th place in popularity Pyatnitskaya street
• Cafe is the most popular category

Streets with a single catering facility

street_dull = data['street'].value_counts().reset_index()
street_dull = street_dull.query('street == 1')['index'] 
d4 = data.query('street in @street_dull')
display(d4.groupby(['district','price']).agg({'category':'first', 
                                'rating':'median', 
                                'middle_avg_bill':'median', 
                                'lat':'count'}).reset_index().sort_values(['district', 'lat'], ascending=False))

	district	price	category	rating	middle_avg_bill	lat
28	Южный административный округ	средние	coffee_shop	4.20	300.0	10
26	Южный административный округ	выше среднего	cafe	4.35	1200.0	2
27	Южный административный округ	низкие	canteen	3.90	162.5	2
25	Южный административный округ	высокие	restaurant	4.10	3000.0	1
24	Юго-Западный административный округ	средние	fast food	4.00	562.5	2
23	Юго-Западный административный округ	низкие	cafe	3.70	100.0	1
22	Юго-Восточный административный округ	средние	cafe	4.30	512.5	9
21	Юго-Восточный административный округ	выше среднего	bar,pub	4.30	1500.0	3
20	Юго-Восточный административный округ	высокие	fast food	4.40	2750.0	1
19	Центральный административный округ	средние	coffee_shop	4.30	462.5	41
16	Центральный административный округ	высокие	bar,pub	4.50	2250.0	16
17	Центральный административный округ	выше среднего	restaurant	4.40	1300.0	13
18	Центральный административный округ	низкие	restaurant	4.20	350.0	1
14	Северо-Западный административный округ	выше среднего	restaurant	4.40	1250.0	3
15	Северо-Западный административный округ	средние	restaurant	4.30	1210.0	3
13	Северо-Восточный административный округ	средние	coffee_shop	4.20	375.0	15
11	Северо-Восточный административный округ	выше среднего	restaurant	4.35	1400.0	2
12	Северо-Восточный административный округ	низкие	coffee_shop	4.20	175.0	2
10	Северо-Восточный административный округ	высокие	restaurant	4.30	3250.0	1
9	Северный административный округ	средние	canteen	4.30	412.5	13
8	Северный административный округ	низкие	coffee_shop	3.95	207.5	4
7	Северный административный округ	выше среднего	bar,pub	4.30	1500.0	3
6	Северный административный округ	высокие	restaurant	4.30	1750.0	2
5	Западный административный округ	средние	bar,pub	4.20	600.0	11
3	Западный административный округ	выше среднего	cafe	4.25	1250.0	2
2	Западный административный округ	высокие	bar,pub	4.10	1750.0	1
4	Западный административный округ	низкие	cafe	4.00	150.0	1
1	Восточный административный округ	средние	bar,pub	4.30	600.0	16
0	Восточный административный округ	выше среднего	cafe	4.10	1150.0	1

fig = px.histogram(d4, x='district_abb', color='category',  
                   color_discrete_sequence= px.colors.qualitative.Vivid)
fig.update_layout(xaxis={'categoryorder':'total descending'})
fig.update_layout(title='Distribution of streets with a single institution by districts and categories', 
                  xaxis_title='Administrative district of Moscow',
                  yaxis_title='Number of establishments')
fig.show()

display(f'Number of streets with a single catering facility - {len(street_dull)}')
display('Median rating of individual establishments' ,d4['rating'].median())
display('Median average check of individual establishments' ,d4['middle_avg_bill'].median())

'Number of streets with a single catering facility - 458'

'Median rating of individual establishments'

4.3

'Median average check of individual establishments'

625.0

---

Total streets with one institution in Moscow - 458 It is typical for such establishments:

• a good rating relative to, for example, the top 15 establishments in Moscow - median 4.3 vs 4.2 in the Top 15
• a fairly high median average check of individual establishments - 625 ₽ VS 415 ₽ in the Top 15
• most of these streets in the Cental AD
• the most numerous category is cafes and coffee shops

The effect of distance from the center on prices in establishments

#
moscow_lat, moscow_lng = 55.751244, 37.618423
    
pth3 = 'admin_level_geomap.geojson'
pth4 = 'datasets/admin_level_geomap.geojson'

if os.path.exists(pth3):
    with open(pth3, 'r',  encoding='utf-8') as f:
        state_geo = json.load(f)
elif os.path.exists(pth4):
    with open(pth4, 'r',  encoding='utf-8') as f:
        state_geo = json.load(f)
else:
    state_geo = 'datasets/admin_level_geomap.geojson'
    
distr_bill = data.groupby('district', as_index=False)['middle_avg_bill'].agg('median')

m = Map(location=[moscow_lat, moscow_lng], zoom_start=10)

Choropleth(
    geo_data=state_geo,
    data=distr_bill,
    columns=['district', 'middle_avg_bill'],
    key_on='feature.name',
    fill_color='YlOrRd',
    fill_opacity=0.5,
    legend_name='Median price of establishments by district').add_to(m)
m

Make this Notebook Trusted to load map: File -> Trust Notebook

---

The distance from the center does not affect the average check.
The average check is determined by the status of the district. In more expensive/elite/historical areas, the prices are higher - the Eastern AD is on a par with the Centeal AD here

Dependence of the opening hours of establishments on the location and category of the institution

#
is24_distr = data.groupby('district', as_index=False)['is_24/7'].agg('mean')

moscow_lat, moscow_lng = 55.751244, 37.618423
    
pth3 = 'admin_level_geomap.geojson'
pth4 = 'datasets/admin_level_geomap.geojson'

if os.path.exists(pth3):
    with open(pth3, 'r',  encoding='utf-8') as f:
        state_geo = json.load(f)
elif os.path.exists(pth4):
    with open(pth4, 'r',  encoding='utf-8') as f:
        state_geo = json.load(f)
else:
    state_geo = 'datasets/admin_level_geomap.geojson'
    
is24_distr = data.groupby('district', as_index=False)['is_24/7'].agg('mean')

m = Map(location=[moscow_lat, moscow_lng], zoom_start=10)

Choropleth(
    geo_data=state_geo,
    data=is24_distr,
    columns=['district', 'is_24/7'],
    key_on='feature.name',
    fill_color='YlOrRd',
    fill_opacity=0.5,
    legend_name='Opening hours of establishments by district').add_to(m)
m

Make this Notebook Trusted to load map: File -> Trust Notebook

---

The map of round-the-clock establishments mirrors the map of prices and ratings.
The more disadvantaged the area, the more round-the-clock establishments - Eastern Administrative District, SEAD
The smallest part of round-the-clock establishments in the Central AD

Distribution of establishments with poor ratings by districts and categories

# Let's determine the value of the "bad rating"
data['rating'].describe()

count    8406.000000
mean        4.229895
std         0.470348
min         1.000000
25%         4.100000
50%         4.300000
75%         4.400000
max         5.000000
Name: rating, dtype: float64

data['rating_rate'] = data['rating'].apply(lambda x: 'low' if x<=4 else 'high')
fig = px.histogram(data[data['rating_rate'] == 'low'], y='district_abb', color='category', 
                   color_discrete_sequence= px.colors.qualitative.Vivid)
fig.update_layout(yaxis={'categoryorder':'total descending'})
fig.update_layout(title='Distribution of establishments with poor ratings by districts and categories', 
                  xaxis_title='Number of establishments',
                  yaxis_title='Administrative district of Moscow ')
fig.show()

---

• There are a lot of establishments with a low rating in the Southern and eastern part of Moscow, Southern Administrative District, NEAD, EAD
• Least of all in the NWAD

Average check of establishments with poor ratings by category

d5 = data.groupby(['category','rating_rate'])['middle_avg_bill'].median().reset_index()
fig = px.bar(d5, x='middle_avg_bill', y='category', color='rating_rate',
                   color_discrete_sequence= px.colors.qualitative.Vivid, barmode='stack')
fig.update_layout(yaxis={'categoryorder':'total descending'})
fig.update_layout(title='Average check of establishments with poor ratings by category', 
                  xaxis_title='Median average check',
                  yaxis_title='Institution category ')
fig.show()

---

• The median average check of establishments with a rating below 4 is significantly less in the bar, pub category
• The rating does not seem to affect the average check of canteens

Interim summary

• Cafes predominate in the categories -28%, restaurants are slightly behind -24%, followed by coffee shops - 16%
• The median number of seats for a coffee shop is 70-80. Most places are in restaurants and bars, pubs - 80-90 seats.
• The majority of non-chain establishments - 62%, network - 38%
• Most often the seeded establishments are bakeries, pizzerias, coffee shops
• The most unpopular category of chain establishments - bar, pub
• Among the Top 15 chain establishments in Moscow, the first place is the coffee shop Chocolate, pizzeria, Yandex-Shop restaurant, and budget coffee shops
• The median rating of chain establishments is 4.2
• Median average check of chain establishments - 415₽
• Most of all establishments are in the Central Administrative District of Moscow. 658 Restaurants, 461 cafes, 425 coffee shops, 360 bars
• Cafes - about 260 establishments and restaurants - about 170 establishments in the district will prevail in all other districts
• The number of bars in the Central AD is 5 times higher than the average of other districts
• Bakeries and canteens are the most unpopular categories in all districts
• Bars have the highest rating by category, fast food has the lowest
• The highest average rating of establishments in the Central AD is 4.38, followed by NAD and NWAD
• The lowest average rating of the Southern Administrative District
• Most of the streets with a single institution in CAD, three times less in NEAD, EAD. In addition to the fact that this may be due - simply to the fact that there are more streets in historical districts, but also that in popular districts not all streets are filled, and in other areas in the blind zone of our dataset there may be many streets left without a single institution at all.
• Number of streets with a single catering facility - 458
• Median rating of individual establishments - 4.3
• Median average check of individual establishments - 625₽
• In more high-status areas, the average check is higher - WAD is on a par with CAD here, although according to the ratings of the institution in the NWAO it is better
• There are the most round-the-clock establishments in the EAD, the SAD, the least in the Central AD. The more disadvantaged the area, the more round-the-clock establishments
• Most of the establishments with a low rating in the Southern and Eastern AD, NEAD, SEAD EAD
• Least of all in the NWAD
• The lowest average check in establishments with a poor rating in the NWAO, the largest in the NEAD

---

Details of the study "opening a coffee shop"

Distribution of coffee shops by districts and features of their location

coffee = data[data['category']=='coffee_shop']
display(f'Number of coffee shops in the dataset', coffee['name'].count())

'Number of coffee shops in the dataset'

1413

#
moscow_lat, moscow_lng = 55.751244, 37.618423
    
pth3 = 'admin_level_geomap.geojson'
pth4 = 'datasets/admin_level_geomap.geojson'

if os.path.exists(pth3):
    with open(pth3, 'r',  encoding='utf-8') as f:
        state_geo = json.load(f)
elif os.path.exists(pth4):
    with open(pth4, 'r',  encoding='utf-8') as f:
        state_geo = json.load(f)
else:
    state_geo = 'datasets/admin_level_geomap.geojson'
    
coffee_distr = coffee.groupby('district', as_index=False)['name'].agg('count')

m = Map(location=[moscow_lat, moscow_lng], zoom_start=10)

marker_cluster = MarkerCluster().add_to(m)

def create_clusters(row):
    icon_url = 'https://img.icons8.com/parakeet/344/restaurant-building.png'
    icon = CustomIcon(icon_url, icon_size=(30,30))

    Marker(
        [row['lat'], row['lng']],
        popup=f"{row['name']} {row['rating']}",
        icon=icon
    ).add_to(marker_cluster)

coffee.apply(create_clusters, axis=1)


Choropleth(
    geo_data=state_geo,
    data=coffee_distr,
    columns=['district', 'name'],
    key_on='feature.name',
    fill_color='YlOrRd',
    fill_opacity=0.5,
    legend_name='Number of coffee shops in the dataset').add_to(m)
m

Make this Notebook Trusted to load map: File -> Trust Notebook

24-hour coffee shops

display('Number of 24-hour coffee shops in Moscow:', len(coffee[coffee['is_24/7'] == True]))

'Number of 24-hour coffee shops in Moscow:'

59

Distribution of coffee shop ratings by district

plt.figure(figsize=(16,6))
order=coffee.groupby('district_abb').mean().sort_values('rating', ascending=False).index
fig = sns.barplot(x='district_abb', y='rating', data=coffee, order=order, ci=None)
fig.set_ylim(4,4.5)
fig.set_title('Average ratings by category of establishments')
fig.set_xlabel('Administrative District of Moscow') 
fig.set_ylabel('Rating of establishments');

#
moscow_lat, moscow_lng = 55.751244, 37.618423

pth3 = 'admin_level_geomap.geojson'
pth4 = 'datasets/admin_level_geomap.geojson'

if os.path.exists(pth3):
    with open(pth3, 'r',  encoding='utf-8') as f:
        state_geo = json.load(f)
elif os.path.exists(pth4):
    with open(pth4, 'r',  encoding='utf-8') as f:
        state_geo = json.load(f)
else:
    state_geo = 'datasets/admin_level_geomap.geojson'
    
coffee_rat = coffee.groupby('district', as_index=False)['rating'].agg('mean')

m = Map(location=[moscow_lat, moscow_lng], zoom_start=10)

Choropleth(
    geo_data=state_geo,
    data=coffee_rat,
    columns=['district', 'rating'],
    key_on='feature.name',
    fill_color='YlOrRd',
    fill_opacity=0.5,
    legend_name='Средний рейтинг заведений по районам').add_to(m)
m

Make this Notebook Trusted to load map: File -> Trust Notebook

Average cost of a cup of cappuccino by district

plt.figure(figsize=(16,6))
order=coffee.groupby('district_abb').mean().sort_values('middle_coffee_cup', ascending=False).index
fig = sns.barplot(x='district_abb', y='middle_coffee_cup', data=coffee, order=order, ci=None)
fig.set_ylim(130,200)
fig.set_title('Average cost of a cup of cappuccino by district')
fig.set_xlabel('Administrative District of Moscow') 
fig.set_ylabel('Average cost of a cup');

Interim summary

The number of coffee shops in the dataset is 1380
Most coffee shops in CAD - 425, NAD - 190, NEAD - 148, WAD - 142
The number of round-the-clock coffee shops in Moscow is 53
The best rating of coffee shops in CAD, NWAD, the lowest - WAD, SAD, NEAD

---

General recommendation for opening a new coffee shop

Consider for a coffee shop the areas with the lowest ratings and the most sparsely populated coffee shops, which means with the most uncompetitive environment
**NAD NEAD SAD

The average cost of a cup of cappuccino in these areas: WAD - 195₽
NEAD - 165₽
SAD - 160₽

Recommendation for opening a new coffee shop. Option 1

Consider coffee shops in the areas selected above.
We will look for the street with the least number of competitors, where there is only one institution on the street

coffee_adv1 = coffee.query('district_abb in ("ЗАО", "ЮАО", "СВАО") & street in @street_dull')
display(f'The number of streets in the WAD, SAD, NEAD with a single institution - {len(coffee_adv1)}')

'The number of streets in the WAD, SAD, NEAD with a single institution - 17'

#
moscow_lat, moscow_lng = 55.751244, 37.618423

m = Map(location=[moscow_lat, moscow_lng], zoom_start=10)

marker_cluster = MarkerCluster().add_to(m)

def create_clusters(row):
    icon_url = 'https://img.icons8.com/parakeet/344/restaurant-building.png'
    icon = CustomIcon(icon_url, icon_size=(30,30))

    Marker(
        [row['lat'], row['lng']],
        popup=f"{row['name']} {row['rating']} {row['street']}",
        icon=icon
    ).add_to(marker_cluster)

coffee_adv1.apply(create_clusters, axis=1)

m

Make this Notebook Trusted to load map: File -> Trust Notebook

---

Based on the small number of such streets - 17, it will be easy to choose the most passable ones with the help of third-party data

Recommendation for opening a new coffee shop. Option 2

Consider the 200 most popular streets in Moscow where there are no coffee shops

street_pop = data['street'].value_counts().reset_index()
# We select 200 popular streets of Moscow
street_pop = street_pop.loc[:200,'index']
coffee_no = data.query('street in @street_pop').pivot_table(index='street', 
                                                            columns = 'category', 
                                                            values = 'name', 
                                                            aggfunc = 'count').reset_index().sort_values('coffee_shop')
# We select from 200 those that do not have coffee shops
coffee_no = coffee_no.query('coffee_shop != coffee_shop')['street']
coffee_adv2 = data.query('street in @coffee_no')
display(f'The number of popular streets in Moscow without coffee shops', len(coffee_adv2.groupby('street')))

'The number of popular streets in Moscow without coffee shops'

17

#
moscow_lat, moscow_lng = 55.751244, 37.618423

m = Map(location=[moscow_lat, moscow_lng], zoom_start=10)

marker_cluster = MarkerCluster().add_to(m)

def create_clusters(row):
    icon_url = 'https://img.icons8.com/parakeet/344/restaurant-building.png'
    icon = CustomIcon(icon_url, icon_size=(30,30))

    Marker(
        [row['lat'], row['lng']],
        popup=f"{row['name']} {row['rating']} {row['street']}",
        icon=icon
    ).add_to(marker_cluster)

coffee_adv2.apply(create_clusters, axis=1)

m

Make this Notebook Trusted to load map: File -> Trust Notebook

---

17 streets is also a small number, it will be easy to choose from it.
You can additionally impose restrictions on the above selected areas.
Or, depending on the business plan, focus on areas for the price of a cup.

---

Presentation: https://disk.yandex.ru/i/kGR5OggR9x2a4g